﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Sales
{
    public class PriceManager:ConnectionManager
    {
        #region Get Pricelist
        public DataTable GetDiscount(string _PriceCode, string _ItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM DISCOUNT WHERE PRICELISTCODE=@PRICELISTCODE AND ITEMCODE=@ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", _PriceCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", _ItemCode));
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public double GetPrice(string PriceCode, string ItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST1 WHERE PRICELISTCODE=@PRICELISTCODE AND ITEMCODE=@ITEMCODE";
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", PriceCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", ItemCode));


            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return Convert.ToDouble(ds.Tables[0].Rows[0]["Price"].ToString());
        }

        public DataRow GetPriceList(string Pricecode)
        {

            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST where PriceListCode=@PriceCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceCode", Pricecode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);
            DataRow _row = null;
            foreach (DataRow oRow in ds.Tables[0].Rows)
            {
                _row = oRow;
            }
            return _row;
        }

        public DataTable GetPriceList()
        {
            try
            {
                SqlCommand oCommand = new SqlCommand();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM PRICELIST";

                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
        }

        public DataTable GetPriceListByPriceCode(string pricecode)
        {

            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST1 where PriceListCode=@PriceCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceCode", pricecode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowsePriceListByCode(string _PriceCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST where PriceListCode LIKE @PriceCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceCode", _PriceCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable BrowsePriceListByName(string _PriceListName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST where PriceListName LIKE @PriceCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceCode", _PriceListName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public bool IsPriceListCodeExists(string _PriceListCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST where PriceListCode=@PriceCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceCode", _PriceListCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsPriceListNameExists(string _PriceListName)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST where PRICELISTNAME=@PriceListName";
            oCommand.Parameters.Add(new SqlParameter("@PriceListName", _PriceListName));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool IsItemCodeExistsInPriceList(string _PriceListCode, string _ItemCode)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM PRICELIST1 where PriceListCode=@PriceListCode and ItemCode=@ItemCode";
            oCommand.Parameters.Add(new SqlParameter("@PriceListCode", _PriceListCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", _ItemCode));

            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion


        #region Add Price Code/Price List
        public void AddNewPriceListCode(PriceUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO PRICELIST (PRICELISTCODE, PRICELISTNAME, REMARKS, BASEPRICELISTCODE, FACTOR, DOCSTATUS) VALUES (@PRICELISTCODE, @PRICELISTNAME, @REMARKS, @BASEPRICELISTCODE, @FACTOR, 'ACTIVE')";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceListCode));
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTNAME", oUnit.PriceListName));
            oCommand.Parameters.Add(new SqlParameter("@REMARKS", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@BASEPRICELISTCODE", oUnit.BasePriceList));
            oCommand.Parameters.Add(new SqlParameter("@FACTOR", oUnit.Factor));
            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO PRICELIST1  " +
                                    "SELECT	@PRICELISTCODE PRICELISTCODE, " +
		                                    "@PRICELISTNAME PRICELISTNAME, " +
		                                    "PRICELIST1.ITEMCODE, PRICELIST1.ITEMNAME," +
		                                    "(SELECT TOP 1 A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) BASEPRICE, " +
                                            "(SELECT TOP 1 A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) + ((SELECT TOP 1 A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) * @FACTOR) AS PRICE  " +
		                                    "FROM PRICELIST1 " +
                                            "WHERE PRICELISTCODE = @BASEPRICELIST";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceListCode));
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTNAME", oUnit.PriceListName));
            oCommand.Parameters.Add(new SqlParameter("@REMARKS", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@BASEPRICELIST", oUnit.BasePriceList));
            oCommand.Parameters.Add(new SqlParameter("@FACTOR", oUnit.Factor));
            oCommand.ExecuteNonQuery();

        }

        public void UpdatePriceList(PriceUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();



            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE PRICELIST SET BASEPRICELISTCODE=@BASEPRICELISTCODE, FACTOR=@FACTOR WHERE PRICELISTCODE=@PRICELISTCODE";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceListCode));
            oCommand.Parameters.Add(new SqlParameter("@BASEPRICELISTCODE", oUnit.BasePriceList));
            oCommand.Parameters.Add(new SqlParameter("@FACTOR", oUnit.Factor));
            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE PRICELIST1 WHERE PRICELISTCODE=@PRICELISTCODE";
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceListCode));

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO PRICELIST1  " +
                                    "SELECT	@PRICELISTCODE PRICELISTCODE, " +
                                            "@PRICELISTNAME PRICELISTNAME, " +
                                            "PRICELIST1.ITEMCODE, PRICELIST1.ITEMNAME," +
                                            "(SELECT A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) BASEPRICE, " +
                                            "(SELECT A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) + ((SELECT A.PRICE FROM PRICELIST1 A WHERE A.PRICELISTCODE=@BASEPRICELIST AND A.ITEMCODE=PRICELIST1.ITEMCODE) * @FACTOR) AS PRICE  " +
                                            "FROM PRICELIST1 " +
                                            "WHERE PRICELISTCODE = @BASEPRICELIST";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceListCode));
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTNAME", oUnit.PriceListName));
            oCommand.Parameters.Add(new SqlParameter("@REMARKS", oUnit.Remarks));
            oCommand.Parameters.Add(new SqlParameter("@BASEPRICELIST", oUnit.BasePriceList));
            oCommand.Parameters.Add(new SqlParameter("@FACTOR", oUnit.Factor));
            oCommand.ExecuteNonQuery();
        }

        public void AddPrice(PriceUnitLineItems oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO PRICELIST1 (PRICELISTCODE, PRICELISTNAME, ITEMCODE, ITEMNAME, PRICE) VALUES (@PRICELISTCODE, @PRICELISTNAME, @ITEMCODE, @ITEMNAME, @PRICE)";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceCode));
            oCommand.Parameters.Add(new SqlParameter("@PRICELISTNAME", oUnit.PriceName));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMNAME", oUnit.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@PRICE", oUnit.Price));

            oCommand.ExecuteNonQuery();

        }

        public void UpdatePrice(PriceUnitLineItems oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE PRICELIST1 SET PRICE = @PRICE WHERE PRICELISTCODE=@PRICELISTCODE AND ITEMCODE=@ITEMCODE";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@PRICE", oUnit.Price));

            oCommand.ExecuteNonQuery();

        }

        public void AddDiscount(PriceUnitDiscounts oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO DISCOUNT (PRICELISTCODE, ITEMCODE, DISCOUNTNAME, PERCENTAGE, TOTAL_DISCOUNT) VALUES (@PRICELISTCODE, @ITEMCODE, @DISCOUNTNAME, @PERCENTAGE, @TOTAL_DISCOUNT)";

            oCommand.Parameters.Add(new SqlParameter("@PRICELISTCODE", oUnit.PriceCode));
            oCommand.Parameters.Add(new SqlParameter("@ITEMCODE", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@DISCOUNTNAME", oUnit.DiscountName));
            oCommand.Parameters.Add(new SqlParameter("@PERCENTAGE", oUnit.DiscountPercent));
            oCommand.Parameters.Add(new SqlParameter("@TOTAL_DISCOUNT", oUnit.TotalDiscount));

            oCommand.ExecuteNonQuery();
        }
        #endregion

    }
}
